#!/bin/bash

APP=edu2077
# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
    do_date=$2
else 
    do_date=`date -d "-1 day" +%F`
fi

dwd_interaction_favor_add_inc="
use edu2077;
set hive.cbo.enable=false;
insert overwrite table ${APP}.dwd_interaction_favor_add_inc partition(dt='$do_date')
select
    data.id,
    data.user_id,
    data.course_id,
    date_format(data.create_time,'yyyy-MM-dd') date_id,
    data.create_time
from ods_favor_info_inc
where dt='$do_date'
and type='insert';
"

dwd_interaction_chapter_comment_inc="
use edu2077;
insert overwrite table ${APP}.dwd_interaction_chapter_comment_inc partition(dt='$do_date')
select
    data.id,
    data.user_id,
    data.chapter_id,
    data.course_id,
    data.comment_txt,
    date_format(data.create_time,'yyyy-MM-dd') date_id,
    data.create_time
from ods_comment_info_inc
where dt='$do_date'
and type='insert';
"

dwd_interaction_course_comment_inc="
use edu2077;
insert overwrite table ${APP}.dwd_interaction_course_comment_inc partition(dt='$do_date')
select
    data.id,
    data.user_id,
    data.course_id,
    data.review_txt,
    data.review_stars,
    date_format(data.create_time,'yyyy-MM-dd') date_id,
    data.create_time
from ods_review_info_inc
where dt='$do_date'
and type='insert';
"

dwd_trade_pay_detail_suc_inc="
insert overwrite table ${APP}.dwd_trade_pay_detail_suc_inc partition(dt='$do_date')
insert overwrite table dwd_trade_pay_detail_suc_inc partition (dt='$do_date')
select
    od.id,
    od.order_id,
    od.user_id,
    od.course_id,
    province_id,
    oi.session_id,
    date_format(callback_time,'yyyy-MM-dd') date_id,
    payment_type,
    callback_time,
    od.course_num,
    origin_amount,
    nvl(coupon_reduce,0.0),
    final_amount
from
(
    select
        data.id,
        data.order_id,
        data.user_id,
        data.course_id,
        1 course_num,
        data.origin_amount,
        data.coupon_reduce,
        data.final_amount
    from ods_order_detail_inc
    where (dt = '$do_date' or dt = date_add('$do_date',-1))
    and (type = 'insert' or type = 'bootstrap-insert')
) od
join
(
    select
        data.id,
        data.order_id,
        date_format(data.callback_time,'yyyy-MM-dd') date_id,
        data.payment_type,
        data.callback_time
    from ods_payment_info_inc
    where dt='$do_date'
    and type='update'
    and array_contains(map_keys(old),'payment_status')
    and data.payment_status='1602'
) pi
on od.order_id=pi.order_id
left join
(
    select
        data.id,
        data.province_id,
        data.session_id
    from ods_order_info_inc
    where (dt = '$do_date' or dt = date_add('$do_date',-1))
    and (type = 'insert' or type = 'bootstrap-insert')
) oi
on od.order_id = oi.id;
"

dwd_user_login_inc="
insert overwrite table ${APP}.dwd_user_login_inc partition(dt='$do_date')
select
    user_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') login_time,
    channel,
    province_id,
    version_code,
    mid_id,
    brand,
    model,
    operate_system
from
    (
        select
            user_id,
            channel,
            area_code,
            version_code,
            mid_id,
            brand,
            model,
            operate_system,
            ts
        from
            (
                select
                    user_id,
                    channel,
                    area_code,
                    version_code,
                    mid_id,
                    brand,
                    model,
                    operate_system,
                    ts,
                    row_number() over (partition by session_id order by ts) rn
                from
                    (
                        select
                            user_id,
                            channel,
                            area_code,
                            version_code,
                            mid_id,
                            brand,
                            model,
                            operate_system,
                            ts,
                            concat(mid_id,'-',last_value(session_start_point,true) over(partition by mid_id order by ts)) session_id
                        from
                            (
                                select
                                    common.uid user_id,
                                    common.ch channel,
                                    common.ar area_code,
                                    common.vc version_code,
                                    common.mid mid_id,
                                    common.ba brand,
                                    common.md model,
                                    common.os operate_system,
                                    ts,
                                    if(page.last_page_id is null,ts,null) session_start_point
                                from ods_log_inc
                                where dt='$do_date'
                                  and page is not null
                            )t1
                    )t2
                where user_id is not null
            )t3
        where rn=1
    )t4
        left join
    (
        select
            id province_id,
            area_code
        from ods_base_province_full
        where dt='$do_date'
    )bp
    on t4.area_code=bp.province_id;
"

dwd_trade_cart_full="
insert overwrite table ${APP}.dwd_trade_cart_full partition(dt='$do_date')
insert overwrite table dwd_trade_cart_full partition(dt='$do_date')
select
    id,
    user_id,
    course_id,
    course_name,
    1 course_name
from ods_cart_info_full
where dt='$do_date'
and sold='0';
"

dwd_trade_trade_flow_acc="
insert overwrite table ${APP}.dwd_trade_trade_flow_acc partition(dt='$do_date')
select
    oi.order_id,
    user_id,
    province_id,
    order_date_id,
    order_time,
    nvl(oi.payment_date_id,pi.payment_date_id),
    nvl(oi.payment_time,pi.payment_time),
    nvl(oi.payment_time,'2020-01-01'),
    nvl(oi.payment_time,'2020-01-01'),
    order_original_amount,
    order_coupon_amount,
    order_total_amount,
    nvl(oi.payment_amount,pi.payment_amount),
    nvl(nvl(oi.payment_time,order_time),'9999-12-31')
from
(
    select
        order_id,
        user_id,
        province_id,
        order_date_id,
        order_time,
        payment_date_id,
        payment_time,
        order_original_amount,
        order_coupon_amount,
        order_total_amount,
        payment_amount
    from dwd_trade_trade_flow_acc
    where dt='9999-12-31'
    union all
    select
        data.id,
        data.user_id,
        data.province_id,
        date_format(data.create_time,'yyyy-MM-dd') order_date_id,
        data.create_time,
        null payment_date_id,
        null payment_time,
        data.origin_amount,
        data.coupon_reduce,
        data.final_amount,
        null payment_amount
    from ods_order_info_inc
    where dt='$do_date'
    and type='insert'
)oi
left join
(
    select
        data.order_id,
        date_format(data.callback_time,'yyyy-MM-dd') payment_date_id,
        data.callback_time payment_time,
        data.total_amount payment_amount
    from ods_payment_info_inc
    where dt='$do_date'
    and type='update'
    and array_contains(map_keys(old),'payment_status')
    and data.payment_status='1602'
)pi
on oi.order_id=pi.order_id;
"

dwd_user_register_inc="
insert overwrite table ${APP}.dwd_user_register_inc partition(dt)
select
    ui.user_id,
    date_format(create_time,'yyyy-MM-dd') date_id,
    create_time,
    channel,
    province_id,
    version_code,
    mid_id,
    brand,
    model,
    operate_system,
    source_id,
    session_id,
    date_format(create_time,'yyyy-MM-dd')
from
    (
        select
            data.id user_id,
            data.create_time
        from ods_user_info_inc
        where dt='$do_date'
          and type='insert'
    )ui
        left join
    (
        select
            user_id,
            area_code,
            brand,
            channel,
            model,
            mid_id,
            source_id,
            session_id,
            operate_system,
            version_code
        from
            (
                select
                    common.ar area_code,
                    common.ba brand,
                    common.ch channel,
                    common.md model,
                    common.mid mid_id,
                    common.os operate_system,
                    common.sc source_id,
                    common.sid session_id,
                    common.uid user_id,
                    common.vc version_code,
                    ts,
                    row_number() over (partition by common.uid order by ts ) as rn
                from ods_log_inc
                where dt='$do_date'
                  and displays is not null
            ) as t1
        where rn=1
    )log
    on ui.user_id=log.user_id
        left join
    (
        select
            id province_id,
            area_code
        from ods_base_province_full
        where dt='$do_date'
    )bp
    on log.area_code=bp.province_id;
"

dwd_trade_order_detail_inc="
insert overwrite table ${APP}.dwd_trade_order_detail_inc partition (dt='$do_date')
select
data.id as id,
data.user_id as user_id,
data.course_id as course_id,
data.order_id as order_id,
data.create_time as create_time,
data.coupon_reduce as coupon_reduce,
data.origin_amount as origin_amount,
data.final_amount as final_amount
from ods_order_detail_inc
where dt = '$do_date';
"

dwd_trade_cart_add_inc="
insert overwrite table ${APP}.dwd_trade_cart_add_inc partition (dt='$do_date')
select
    id,
    user_id,
    session_id,
    course_id,
    create_time
from ods_cart_info_full
where dt = '$do_date';
"

dwd_test_user_exam_question_inc="
set hive.cbo.enable=false;
insert overwrite table ${APP}.dwd_test_user_exam_question_inc partition (dt='$do_date')
select
       e.id,
       user_id,
       paper_id,
       exam_id,
       question_id,
       is_correct,
       score,
       duration_sec,
       create_time
from
(
    select data.id                           as id,
           data.user_id                      as user_id,
           data.paper_id                     as paper_id,
           cast(data.score as bigint)        as score,
           cast(data.duration_sec as bigint) as duration_sec,
           data.create_time                  as create_time
    from ods_test_exam_inc
    where dt='$do_date'
    and type='bootstrap-insert'
)e
left join
(
    select
        data.exam_id  exam_id,
       data.question_id  question_id,
        data.is_correct  is_correct
    from ods_test_exam_question_inc
    where dt='$do_date'
)teq
on e.id=teq.exam_id;
"

dwd_video_user_chapter_process_inc="
insert overwrite table ${APP}.dwd_video_user_chapter_process_inc partition(dt='$do_date')
select
       user_id,
       video_id,
       oci.course_id,
       position_sec,
       play_sec,
       create_time
from
(select
       common.uid     as user_id,
       appvideo.video_id   as video_id,
       --course_id  as course_id,
       cast(appvideo.position_sec as bigint) as position_sec  ,
       cast(appvideo.play_sec as bigint) as play_sec  ,
       from_unixtime(cast((ts/1000) as bigint),'yyyy-MM-dd HH:mm:ss')    as create_time
from ods_log_inc
where dt = '$do_date'
and appvideo.video_id is not null
and appvideo.play_sec is not null)
ol
left join
(select
    course_id,
    video_id    v
from ods_chapter_info_full
where dt = '$do_date')oci
on ol.video_id=oci.v
"

dwd_traffic_page_view_inc="
set hive.cbo.enable=false;
insert overwrite table ${APP}.dwd_traffic_page_view_inc partition(dt='$do_date')
select
    province_id,
    brand,
    channel,
    is_new,
    model,
    mid_id,
    operate_system,
    user_id,
    version_code,
    page_item,
    page_item_type,
    last_page_id,
    page_id,
    source_id,
    bs.source_site,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') view_time,
    concat(mid_id,'-',last_value(session_start_point,true) over (partition by mid_id order by ts)) session_id,
    during_time
from
(
    select
        common.ar area_code,
        common.ba brand,
        common.ch channel,
        common.is_new is_new,
        common.md model,
        common.mid mid_id,
        common.os operate_system,
        common.uid user_id,
        common.vc version_code,
        common.sc source_id,
        page.during_time,
        page.item page_item,
        page.item_type page_item_type,
        page.last_page_id,
        page.page_id,
        ts,
        if(page.last_page_id is null,ts,null) session_start_point
    from ods_log_inc
    where dt='$do_date'
    and page is not null
)log
left join
(
    select
        id province_id,
        area_code
    from ods_base_province_full
    where dt='$do_date'
)bp
on log.area_code=bp.province_id
left join
(
    select
        id,
        source_site
    from ods_base_source_full
) bs
on log.source_id = bs.id;
"


dwd_traffic_appvideo_play_inc="
use edu2077;
set hive.cbo.enable=false;
insert overwrite table ${APP}.dwd_traffic_appvideo_play_inc partition(dt='$do_date')
select
    common.uid,
    common.sid,
    appvideo.video_id,
    appvideo.play_sec,
    appvideo.position_sec,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') view_time,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id
from ods_log_inc
where dt='$do_date'
and appvideo.video_id is not null;
"

dwd_video_audition_play_inc="
set hive.cbo.enable=false;
insert overwrite table ${APP}.dwd_video_audition_play_inc partition(dt='$do_date')
select
       t2.sid,
       t2.uid,
       t2.video_id,
       t3.chapter_id,
       t2.is_first,
       t2.view_time,
       t2.date_id,
       t3.course_id,
       t3.course_name,
       t3.subject_id,
       t3.subject_name,
       t3.category_id,
       t3.category_name
from (
         select sid,
                uid,
                video_id,
                if(play_sec = position_sec, 1, 0) is_first,
                view_time,
                date_id
         from (
                  select uid,
                         sid,
                         video_id,
                         play_sec,
                         position_sec,
                         view_time,
                         date_id,
                         row_number() over (partition by uid,sid,video_id order by position_sec) rt
                  from dwd_traffic_appvideo_play_inc
                  where dt = '$do_date'
              ) t1
         where rt = 1
     ) t2
left join (
    select
           video_id,
           chapter_num chapter_id,
           id course_id,
           course_name,
           subject_id,
           subject_name,
           category_id,
           category_name
    from
    dim_course_full
    where dt='$do_date'
) t3 on t2.video_id=t3.video_id;
"

case $1 in
    "dwd_interaction_favor_add_inc" )
        hive -e "$dwd_interaction_favor_add_inc"
    ;;
    "dwd_interaction_chapter_comment_inc" )
        hive -e "$dwd_interaction_chapter_comment_inc"
    ;;
    "dwd_interaction_course_comment_inc" )
        hive -e "$dwd_interaction_course_comment_inc"
    ;;
    "dwd_trade_pay_detail_suc_inc" )
        hive -e "$dwd_trade_pay_detail_suc_inc"
    ;;   
    "dwd_trade_cart_full" )
        hive -e "$dwd_trade_cart_full"
    ;;  
    "dwd_trade_trade_flow_acc" )
        hive -e "$dwd_trade_trade_flow_acc"
    ;;
    "dwd_user_login_inc" )
        hive -e "$dwd_user_login_inc"
    ;;
    "dwd_traffic_page_view_inc" )
        hive -e "$dwd_traffic_page_view_inc"
    ;;
    "dwd_user_register_inc" )
        hive -e "$dwd_user_register_inc"
    ;;   
    "dwd_trade_order_detail_inc" )
        hive -e "$dwd_trade_order_detail_inc"
    ;; 
	"dwd_trade_cart_add_inc" )
        hive -e "$dwd_trade_cart_add_inc"
    ;; 
	"dwd_video_user_chapter_process_inc" )
        hive -e "$dwd_video_user_chapter_process_inc"
    ;; 
	"dwd_test_user_exam_question_inc" )
        hive -e "$dwd_test_user_exam_question_inc"
    ;;
	"dwd_traffic_appvideo_play_inc" )
        hive -e "$dwd_traffic_appvideo_play_inc"
    ;; 
	"dwd_video_audition_play_inc" )
        hive -e "$dwd_video_audition_play_inc"
    ;; 	
    "all" )
        hive -e "$dwd_interaction_favor_add_inc$dwd_interaction_chapter_comment_inc$dwd_interaction_course_comment_inc$dwd_trade_pay_detail_suc_inc$dwd_trade_cart_full$dwd_trade_trade_flow_acc$dwd_user_login_inc$dwd_traffic_page_view_inc$dwd_user_register_inc$dwd_trade_order_detail_inc$dwd_trade_cart_add_inc$dwd_video_user_chapter_process_inc$dwd_test_user_exam_question_inc$dwd_traffic_appvideo_play_inc$dwd_video_audition_play_inc"
    ;;
esac
